import 'package:part_time_job_pro/common/models/books.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';

class DatabaseHelper {
  //数据库名
  static const _dbName = 'myDatabase.db';

  //版本
  static const _dbVersion = 1;

  //表名
  static const booksTable = 'books';

  //数据类型
  static const idType = 'INTEGER PRIMARY KEY AUTOINCREMENT'; //ID
  static const textType = 'TEXT NOT NULL'; //字符串
  static const boolType = 'BOOLEAN NOT NULL'; //boolean
  static const integerType = 'INTEGER NOT NULL'; //浮点型
  static const realType = 'REAL NOT NULL'; //浮点型
  static const dateTimeType = 'DATETIME NOT NULL'; //时间类型

  //私有构造
  DatabaseHelper._privateConstructor();

  //外部实例
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  static Database? _database;

  Future<Database> get database async => _database ??= await _initDatabase();

  //初始化对象
  _initDatabase() async {
    var documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, _dbName);
    return await openDatabase(path,
        version: _dbVersion, onCreate: _onCreate, onUpgrade: _onUpgrade);
  }

  Future _onCreate(Database db, int version) async {
    // 创建books表
    await db.execute('''
          CREATE TABLE $booksTable (
            ${BookFields.book_id} $idType,
            ${BookFields.book_num} $integerType,
            ${BookFields.book_name} $textType,
            ${BookFields.book_desc} $textType,
            ${BookFields.is_checked} $realType DEFAULT 0,
            ${BookFields.create_time} $dateTimeType DEFAULT CURRENT_TIMESTAMP
          )
          ''');

    // 创建其他表...
  }

  // 更新数据库结构时使用的方法
  Future _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < newVersion) {
      // 执行数据库升级相关的操作
      if (oldVersion == 1) {
        // 从版本1升级到版本2: 添加新表orders
        await db.execute('''
         CREATE TABLE orders (
           id INTEGER PRIMARY KEY,
           user_id INTEGER,
           total REAL NOT NULL,
           created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
       ''');
      }
      // 如果有更多的版本更新，可以继续添加else if语句来处理
      // 例如，如果你想要从版本2升级到版本3，你可以这样做：
      /*
     else if (oldVersion == 2) {
       // 从版本2升级到版本3: 添加新列到现有表
       await db.execute('ALTER TABLE users ADD COLUMN email TEXT');
    }
     */
    }
  }

  //插入数据
  Future<int> insertBookByMap(Map<String, dynamic> row) async {
    Database db = await instance.database;
    return await db.insert(booksTable, row);
  }

  //插入数据
  Future<Books> insertBook(Books books) async {
    Database db = await instance.database;
    int id = await db.insert(booksTable, books.toJson());
    return books.copy(bookId: id);
  }

  //查询数据
  Future<List<Books>> queryAllBooks() async {
    //获取实例
    Database db = await instance.database;
    //时间排序
    String orderBy = '$orderColumn $orderType';
    //查询结果
    final result = await db.query(booksTable, orderBy: orderBy);
    //转换数据
    return result.map((json) => Books.fromJson(json)).toList();
  }

  //查询数据
  Future<Books?> queryBooksById(int id) async {
    //获取实例
    Database db = await instance.database;
    //查询结果
    List<Map<String, dynamic>> resultList = await db
        .query(booksTable, where: '${BookFields.book_id}= ?', whereArgs: [id]);
    //转换数据
    return resultList.isNotEmpty ? Books.fromJson(resultList.first) : null;
  }

  //更新数据 map传递
  Future<int> updateBooksByMap(Map<String, dynamic> row) async {
    //获取实例
    Database db = await instance.database;
    //更新数据
    int id = row[BookFields.book_id];
    return await db.update(booksTable, row,
        where: '${BookFields.book_id} = ?', whereArgs: [id]);
  }

  Future<int> updateBooksByEntity(Books book) async {
    //获取实例
    final db = await instance.database;
    //更新数据
    return db.update(
      booksTable,
      book.toJson(),
      where: '${BookFields.book_id} = ?',
      whereArgs: [book.bookId],
    );
  }

  //删除数据
  Future<int> deleteBookById(int id) async {
    //获取实例
    Database db = await instance.database;
    //删除数据
    return await db
        .delete(booksTable, where: '${BookFields.book_id}= ?', whereArgs: [id]);
  }
}
